﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using OgilvyOne.UKNOW.Entity;
using System.Web;
using OgilvyOne.CommonToolLibrary;

using System.Data;
using OgilvyOne.CommonToolLibrary.MD5;
using OgilvyOne.SqlServerDAL;
using OgilvyOne.BLL.BLLEngine;

namespace OgilvyOne.UKNOW.EntityDB
{
    
    public  class UserInfoDB
    {
        private const string PageConfigXML = @"XML\PageConfig.xml";
        /// <summary>
        /// 
        /// </summary>
        /// <param name="memberInfo">UserInfo object</param>
        /// <param name="strKey">Pageconfig.xml Name </param>
        /// <param name="registerType">1: 中文注册 0:英文注册</param>
        /// <returns></returns>
        public static int Insert(UserInfo memberInfo,string strKey,string registerType)
        {
            int i = -1;
            try
            {
                string sql = string.Empty;
                sql = ConfigListInfoDB.GetItemValue(HttpContext.Current.Server.MapPath(PageConfigXML), "SqlConfig", strKey);
                sql = sql.Replace("[$USERNAME]", memberInfo.UserName);
                sql = sql.Replace("[$PASSWORD]", MD5.GetMD5(memberInfo.Password));
                sql = sql.Replace("[$MEMBER_NAME]", memberInfo.UserName);
                sql = sql.Replace("[$USER_TYPE]", "0");
                sql = sql.Replace("[$MOBLIE]", memberInfo.Mobile);
                sql = sql.Replace("[$Register_TIME]", DateTime.Now.ToString());
                sql = sql.Replace("[$RegisterType]",registerType);
                sql = sql.Replace("[$STATE]", "0");
                sql = sql.Replace("[$EMAIL]", memberInfo.Email);
                sql = sql.Replace("[$BIRTHDAY]", "''");
                sql = sql.Replace("[$StudyAbroad]", memberInfo.StudyAbroad);
                sql = sql.Replace("[$IELTS]", memberInfo.IELTS);
                sql = sql.Replace("[$ArtsAndCulture]", memberInfo.ArtsAndCulture);
                sql = sql.Replace("[$Sina]", memberInfo.Sina);
                sql = sql.Replace("[$Sohu]", memberInfo.Sohu);
                sql = sql.Replace("[$Tengxun]", memberInfo.Tengxun);
                sql = sql.Replace("[$SinaWeibo]", memberInfo.SinaWeibo);
                sql = sql.Replace("[$TengXunWeibo]", memberInfo.TengXunWeibo);
                sql = sql.Replace("[$Kaixin]", memberInfo.Kaixin);
                sql = sql.Replace("[$Douban]", memberInfo.Douban);
                sql = sql.Replace("[$Renren]", memberInfo.Renren);
                sql = sql.Replace("[$Google]", memberInfo.Google);
                sql = sql.Replace("[$Baidu]", memberInfo.Baidu);
                sql = sql.Replace("[$Bing]", memberInfo.Bing);
                sql = sql.Replace("[$OfflineEvents]",memberInfo.OfflineEvents);	
                sql = sql.Replace("[$Newspaper]",memberInfo.Newspaper); 		
                sql = sql.Replace("[$Outdoor]",memberInfo.Outdoor); 			
                sql = sql.Replace("[$Taxi]",memberInfo.Taxi); 					
                sql = sql.Replace("[$Metro]",memberInfo.Metro);
                sql = sql.Replace("[$TV]", memberInfo.TV); 						
                sql = sql.Replace("[$BBS]", memberInfo.BBS);
                sql = sql.Replace("[$Recommendtion]", memberInfo.Recommendtion);
                sql = sql.Replace("[$OtherChannels]", memberInfo.OtherChannels);
                //i= CommonQuery.ExecuteNonQueryBySql(Env.DefaultConnectionString, sql);
                i = SQLHelper.ExecuteSql(sql);
            }
            catch(Exception e)
            {
                
            }
            return i;
        }

        public static DataTable SelectByUserName(UserInfo memberInfo,string strKey)
        {
            string sql = string.Empty;
            sql = ConfigListInfoDB.GetItemValue(HttpContext.Current.Server.MapPath(PageConfigXML), "SqlConfig", strKey);
            sql = sql.Replace("[$USERNAME]", memberInfo.UserName);
            DataTable dt = CommonQuery.GetDataBySql(Env.DefaultConnectionString, sql);
            return dt;
        }
        public static DataTable SelectByUserName(string userName, string strKey)
        {
            string sql = string.Empty;
            sql = ConfigListInfoDB.GetItemValue(HttpContext.Current.Server.MapPath(PageConfigXML), "SqlConfig", strKey);
            sql = sql.Replace("[$USERNAME]", userName);
            DataTable dt =  SQLHelper.Query(sql).Tables[0];
            return dt;
        }

        public static DataTable SelectByMail(string mail, string strKey)
        {
            string sql = string.Empty;
            sql = ConfigListInfoDB.GetItemValue(HttpContext.Current.Server.MapPath(PageConfigXML), "SqlConfig", strKey);
            sql = sql.Replace("[$EMAIL]", mail);
            DataTable dt = SQLHelper.Query(sql).Tables[0];
            return dt;
        }

        /// <summary>
        /// 按时间段进行查询
        /// </summary>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <param name="strKey"></param>
        /// <returns></returns>
        public static DataTable Query(string startTime, string endTime,string userType, string strKey)
        {
            string sql = string.Empty;
            sql = ConfigListInfoDB.GetItemValue(HttpContext.Current.Server.MapPath(PageConfigXML), "SqlConfig", strKey);
            sql = sql.Replace("[$StartTime]", startTime);
            sql = sql.Replace("[$EndTime]", endTime);
            sql = sql.Replace("[$RegisterType]", userType);
            DataTable dt = SQLHelper.Query(sql).Tables[0];
            return dt;
        }

        public static DataTable SelectById(string id)
        {
            string sql = string.Empty;
            sql = string.Format("select * from uknow_member_info where MEMBER_ID={0}", id);
            DataTable dt = SQLHelper.Query(sql).Tables[0];
            return dt;
        }

        public static DataTable QueryByTime(string startTime, string endTime)
        {
            string sql = string.Empty;
            sql = string.Format("select * from uknow_member_info where datediff(dd, last_login_time, '{0}')<=0 and datediff(dd, last_login_time, '{1}')>=0", startTime, endTime);
            DataTable dt = SQLHelper.Query(sql).Tables[0];
            return dt;
        }

        public static DataTable QueryByAll(string startTime, string endTime,string name,string email)
        {
            string sql = string.Empty;
            sql = string.Format("select * from uknow_member_info where datediff(dd, last_login_time, '{0}')<=0 and datediff(dd, last_login_time, '{1}')>=0 and (MEMBER_NAME like '%{2}%' or  EMAIL like '%{3}%')", startTime,endTime,name,email);
            DataTable dt = SQLHelper.Query(sql).Tables[0];
            return dt;
        }

        public static DataTable QueryByName(string startTime, string endTime, string name)
        {
            string sql = string.Empty;
            sql = string.Format("select * from uknow_member_info where datediff(dd, last_login_time, '{0}')<=0 and datediff(dd, last_login_time, '{1}')>=0 and (MEMBER_NAME like '%{2}%')", startTime, endTime, name);
            DataTable dt = SQLHelper.Query(sql).Tables[0];
            return dt;
        }

        public static DataTable QueryByEmail(string startTime, string endTime, string email)
        {
            string sql = string.Empty;
            sql = string.Format("select * from uknow_member_info where datediff(dd, last_login_time, '{0}')<=0 and datediff(dd, last_login_time, '{1}')>=0 and (EMAIL like '%{2}%')", startTime, endTime, email);
            DataTable dt = SQLHelper.Query(sql).Tables[0];
            return dt;
        }





        public static int Update(string idLst,string strKey)
        {
            int i = -1;

            try
            {
                string sql = string.Empty;
                sql = ConfigListInfoDB.GetItemValue(HttpContext.Current.Server.MapPath(PageConfigXML), "SqlConfig", strKey);
                sql = sql.Replace("[$Member_ID]", idLst);
                i = SQLHelper.ExecuteSql(sql);
            }
            catch(Exception e)
            {
                
            }
            return i;
        }

        public static int Delete(string idLst)
        {
            int i = -1;

            try
            {
                string sql = string.Empty;
                sql = string.Format("delete from uknow_member_info where MEMBER_ID={0}", idLst);
                i = SQLHelper.ExecuteSql(sql);
            }
            catch (Exception e)
            {

            }
            return i;
        }
    }
}
